import os
import pandas as pd
import mysql.connector

basic_path = str(os.path.dirname(__file__)) + "\\"
grade_sim_index_path = basic_path + "..\\simer\\sim\\sim_index.csv"

def init_grade_sim_index(recreate=False):
    df = pd.read_csv(grade_sim_index_path, encoding="utf-8-sig")
    con = mysql.connector.connect(
        host="192.168.80.190",
        port=3306,
        user="hive",
        password="12345678",
        database="gaokao"
    )
    cursor = con.cursor()
    columns = ["province_name", "school_name", "min_rank", "choose1", "choose2", "choose3",
               "school_province_name", "is_dual_class", "is_985", "is_211"]
    if recreate:
        drop_query = "DROP TABLE IF EXISTS grade_sim_index"
        cursor.execute(drop_query)
        create_query = "CREATE TABLE grade_sim_index (province_name VARCHAR(20) NOT NULL,school_name VARCHAR(50) NOT " \
                       "NULL,min_rank INT NOT NULL,choose1 VARCHAR(20) DEFAULT '',choose2 VARCHAR(20) DEFAULT ''," \
                       "choose3 VARCHAR(20) DEFAULT '',school_province_name VARCHAR(20) NOT NULL,is_dual_class INT " \
                       "default 0,is_985 INT default 0,is_211 INT default 0)"
        cursor.execute(create_query)
        index_query = "create index province_index on grade_sim_index(province_name)"
        cursor.execute(index_query)
        index_query = "create index min_rank_index on grade_sim_index(min_rank)"
        cursor.execute(index_query)
        index_query = "create index choose1_index on grade_sim_index(choose1)"
        cursor.execute(index_query)
        index_query = "create index choose2_index on grade_sim_index(choose2)"
        cursor.execute(index_query)
        index_query = "create index choose3_index on grade_sim_index(choose3)"
        cursor.execute(index_query)
        con.commit()
    insert_query = "INSERT INTO grade_sim_index (province_name, school_name, min_rank, choose1, choose2, choose3, school_province_name, is_dual_class, is_985, is_211) " \
                   "VALUES ('%s', '%s', %s, '%s', '%s', '%s', '%s', %s, %s, %s)"
    for i in df.iterrows():
        row = i[1]
        q = insert_query % (row["province_name"], row["school_name"], row["min_rank"], row["choose1"], row["choose2"], row["choose3"], row["school_province_name"], row["is_dual_class"], row["is_985"], row["is_211"])
        q = q.replace("nan", "")
        cursor.execute(q)
        con.commit()
    cursor.close()
    con.close()

init_grade_sim_index(True)